package com.gitee.melin.bee.core.jdbc.dialect.sqlserver;

import com.gitee.melin.bee.core.jdbc.dialect.DefaultTypeConvertor;
import com.gitee.melin.bee.core.jdbc.relational.MetaColumn;
import com.gitee.melin.bee.core.jdbc.relational.ConnectionInfo;
import com.gitee.melin.bee.core.jdbc.enums.DataSourceType;
import com.gitee.melin.bee.core.jdbc.dialect.DataTypeConvertor;
import com.gitee.melin.bee.core.jdbc.dialect.AbstractJdbcDialect;
import com.gitee.melin.bee.core.jdbc.dialect.IDBQuery;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.StringUtils;

import java.sql.Connection;
import java.sql.Driver;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class SqlServerJdbcDialect extends AbstractJdbcDialect {

    @Override
    public DataSourceType getDataSourceType() {
        return DataSourceType.SQLSERVER;
    }

    private static final String[] SYS_DBS = new String[] {"GUEST", "CDC", "GUEST", "INFORMATION_SCHEMA", "SYS"};

    public SqlServerJdbcDialect(ConnectionInfo connectionInfo) {
        super(connectionInfo);
    }

    public SqlServerJdbcDialect(Connection connection) {
        super(connection);
    }

    @Override
    protected IDBQuery getDBQuery() {
        return new SqlServerQuery();
    }

    @Override
    protected DataTypeConvertor getTypeConvert() {
        return new DefaultTypeConvertor();
    }

    @Override
    protected Driver getDriver() throws SQLException {
        return loadDriver(DataSourceType.SQLSERVER.getDriverClass());
    }

    @Override
    protected boolean filterSchema(String schemaName) {
        if (StringUtils.startsWithIgnoreCase(schemaName, "db_")) {
            return false;
        }

        if (ArrayUtils.contains(SYS_DBS, schemaName.toUpperCase())) {
            return false;
        }

        return super.filterSchema(schemaName);
    }

    @Override
    protected boolean filterTable(String tableName) {
        return !StringUtils.equalsIgnoreCase(tableName, "systranschemas");
    }

    @Override
    protected String getDDLColumnName() {
        return "";
    }

    @Override
    protected String createTableSql(String schemaName, String tableName) {
        return "select 'create table [' + so.name + '] (' + o.list + ')' + CASE\n" +
                "WHEN tc.Constraint_Name IS NULL THEN ''\n" +
                "ELSE 'ALTER TABLE ' + so.Name +\n" +
                "    ' ADD CONSTRAINT ' + tc.Constraint_Name +\n" +
                "    ' PRIMARY KEY ' +\n" +
                "    ' (' + LEFT(j.List, Len(j.List)-1) + ')' END\n" +
                "        from sysobjects so\n" +
                "            cross apply\n" +
                "            (SELECT\n" +
                "            '  ['+ column_name +'] ' +\n" +
                "            data_type + case data_type\n" +
                "            when 'sql_variant' then ''\n" +
                "            when 'text' then ''\n" +
                "            when 'ntext' then ''\n" +
                "            when 'xml' then ''\n" +
                "            when 'image' then ''\n" +
                "            when 'decimal' then '(' + cast (numeric_precision as varchar) + ', ' + cast (numeric_scale as varchar) + ')'\n" +
                "            else coalesce ('('+ case when character_maximum_length = -1 then 'MAX' else cast (character_maximum_length as varchar) end +')', '') end + ' ' +\n" +
                "            case when exists (\n" +
                "            select id from syscolumns\n" +
                "            where object_name(id)=so.name\n" +
                "            and name = column_name\n" +
                "            and columnproperty(id, name, 'IsIdentity') = 1\n" +
                "            ) then\n" +
                "            'IDENTITY(' +\n" +
                "            cast (ident_seed(so.name) as varchar) + ',' +\n" +
                "            cast (ident_incr(so.name) as varchar) + ')'\n" +
                "            else ''\n" +
                "            end + ' ' +\n" +
                "            (case when IS_NULLABLE = 'No' then 'NOT ' else '' end ) + 'NULL ' +\n" +
                "            case when information_schema.columns.COLUMN_DEFAULT IS NOT NULL THEN 'DEFAULT '+ information_schema.columns.COLUMN_DEFAULT ELSE '' END + ', '\n" +
                "            from information_schema.columns where table_name = so.name\n" +
                "            order by ordinal_position\n" +
                "            FOR XML PATH ('')) o (list)\n" +
                "            left join\n" +
                "            information_schema.table_constraints tc\n" +
                "        on tc.Table_name = so.Name\n" +
                "            AND tc.Constraint_Type = 'PRIMARY KEY'\n" +
                "            cross apply\n" +
                "            (select '[' + Column_Name + '], '\n" +
                "            FROM information_schema.key_column_usage kcu\n" +
                "            WHERE kcu.Constraint_Name = tc.Constraint_Name\n" +
                "            ORDER BY\n" +
                "            ORDINAL_POSITION\n" +
                "            FOR XML PATH ('')) j (list)\n" +
                "        where xtype = 'U'\n" +
                "          AND name = '" + tableName + "'";
    }

    @Override
    public String getCreateTableScript(String databaseName, String tableName) {
        // 进行拼接 基础表结构 , 暂时不包含字段说明信息
        StringBuilder stringBuilder = new StringBuilder("CREATE TABLE [" + databaseName + "].[" + tableName + "] (\n");
        List<String> pkList = new ArrayList<>();
        for (MetaColumn schemaColumn : getSchemaColumns(databaseName, tableName)) {
            String columnName = schemaColumn.getColumnName();
            String columnType = schemaColumn.getColumnType();
            stringBuilder.append("[").append(columnName).append("]").append("\t").append(columnType);
            if (!StringUtils.equalsAnyIgnoreCase(columnType,
                    "int", "bigint", "smallint", "tinyint", "real", "bit", "ntext", "text","date","datetime",
                    "datetime2","datetimeoffset","smalldatetime","time")) {
                // 以上不需要填写 (n)
                if (StringUtils.equalsAnyIgnoreCase(columnType, "decimal", "numeric")) {
                    // 数值类型 需要指定 精度和小数位数
                    stringBuilder.append("(").append(schemaColumn.getPrecision()).append(",").append(schemaColumn.getScale()).append(")");
                } else if (schemaColumn.getColumnLength() == -1) {
                    // 直接拼max
                    stringBuilder.append("(").append("max").append(")");
                } else if (schemaColumn.getColumnLength() != 0) {
                    stringBuilder.append("(").append(schemaColumn.getColumnLength()).append(")");
                }
            }
            if (schemaColumn.isNullable()) {
                stringBuilder.append("\t").append("NULL");
            } else {
                stringBuilder.append("\t").append("NOT NULL");
            }
            if (schemaColumn.isPrimaryKey()) {
                pkList.add(columnName);
            }
            stringBuilder.append(",").append("\n");
        }
        if (pkList.size() != 0) {
            stringBuilder.append("PRIMARY KEY (");
            for (String pk : pkList) {
                stringBuilder.append("[").append(pk).append("]").append(",");
            }
            stringBuilder.deleteCharAt(stringBuilder.length() - 1).append(")");
        } else {
            stringBuilder.delete(stringBuilder.lastIndexOf(","), stringBuilder.length());
        }
        return stringBuilder.append("\n").append(")").toString();
    }
}
